/**
 * Copyright (c) 2020 kedacom
 * OpenATC is licensed under Mulan PSL v2.
 * You can use this software according to the terms and conditions of the Mulan PSL v2.
 * You may obtain a copy of Mulan PSL v2 at:
 * http://license.coscl.org.cn/MulanPSL2
 * THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
 * EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
 * MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
 * See the Mulan PSL v2 for more details.
 **/
package com.openatc.agent.service;

import com.google.gson.*;
import com.openatc.agent.model.StatesCollectYesterday;
import com.openatc.agent.model.User;
import com.openatc.agent.resmodel.PageOR;
import com.openatc.agent.utils.RedisTemplateUtil;
import com.openatc.comm.data.MessageData;
import com.openatc.core.util.DateUtil;
import com.openatc.model.model.AscsBaseModel;
import com.openatc.model.model.DevCover;
import com.openatc.model.model.MyGeometry;
import org.apache.shiro.SecurityUtils;
import org.postgresql.jdbc.PgArray;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.annotation.PostConstruct;
import java.sql.*;
import java.util.Date;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.locks.ReentrantLock;
import java.util.logging.Logger;

import static com.openatc.comm.common.CommunicationType.OCP_PROTYPE;
import static com.openatc.comm.common.CommunicationType.SCP_PROTYPE;

@Repository
public class AscsDao {

    private static Logger logger = Logger.getLogger(AscsDao.class.toString());
    public static  StatesCollectYesterday statesCollectYesterday = new StatesCollectYesterday();

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private RedisTemplateUtil redisTemplate;

    // 保存真实设备ID和Agentid的对应关系，不保存平台设备id和agentid的对应关系
    // 当真实设备新增，更新，或删除时，需更新此map
    private static Map<String, String> thirdidToAgentidOcp = new ConcurrentHashMap<>();
    private final ReentrantLock thridAgentCacheLock = new ReentrantLock();

    // 缓存了所有设备上报的注册消息，key为agentid
    public static Map<String, MessageData> loginMap = new HashMap<>();
    private static final ReentrantLock loginMapCacheLock = new ReentrantLock();
    Gson gson = new Gson();

    @Autowired
    private UserDao userDao;

//    /**
//     * 初始化OCP设备的真实ID和平台ID的映射关系
//     * 此处不映射SCP设备的真实ID和平台ID的映射关系，由各个对接服务负责
//     */
//    @PostConstruct
//    public void initMap(){
//        initThirdidToAgentidOcpMap();
//    }

    public static void putMsgToLoginMap(String key,MessageData value) {
        try {
            loginMapCacheLock.lock();
            loginMap.put(key,value);
        } finally {
            loginMapCacheLock.unlock();
        }
    }

    public static Map<String,MessageData> getLoginMap() {
//        Map<String,MessageData> map = new HashMap<>();
//        try {
//            loginMapCacheLock.lock();
//            for (String key : loginMap.keySet()) {
//                map.put(key,loginMap.get(key));
//            }
//        } finally {
//            loginMapCacheLock.unlock();
//        }
        return loginMap;
    }

    public static void clearLoginMap() {
        try {
            loginMapCacheLock.lock();
            loginMap.clear();
        } finally {
            loginMapCacheLock.unlock();
        }
    }


    public void initThirdidToAgentidOcpMap(){
        try {
            thridAgentCacheLock.lock(); // 加锁

            thirdidToAgentidOcp.clear();
            String sql = "SELECT agentid, thirdplatformid FROM dev where protocol = 'ocp' ";
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
            for(Map<String,Object> m : maps) {
                String agentid = (String)m.get("agentid");
                String thirdpartyid = (String)m.get("thirdplatformid");
                if(thirdpartyid != null) {
                    thirdidToAgentidOcp.put(thirdpartyid,agentid);
                }
            }
        } finally {
            thridAgentCacheLock.unlock(); // 释放锁
        }
    }

    public List<AscsBaseModel> getAscs() {
        String sql = "SELECT id, thirdplatformid, platform, gbid,  agentid, protocol, geometry, code,mode,control, type, status, descs, name, case (LOCALTIMESTAMP - lastTime)< '2 min' when 'true' then 'UP' else 'DOWN' END AS state,lastTime,tags FROM dev ORDER BY agentid";
        List<AscsBaseModel> ascsBaseModels = getDevByPara(sql);

        return ascsBaseModels;
    }

    public List<AscsBaseModel> getAscsLikeCode(String code) {
        if (code.equals("*"))
            return getAscs();
        else {
            String sql = "SELECT id, thirdplatformid, platform, gbid,  agentid, protocol, geometry, code,mode,control, type, status, descs, name, case (LOCALTIMESTAMP - lastTime)< '2 min' when 'true' then 'UP' else 'DOWN' END AS state,lastTime,tags FROM dev where code  like '" + code + "%' ORDER BY agentid";
            List<AscsBaseModel> ascsBaseModels = getDevByPara(sql);

            return ascsBaseModels;
        }

    }

    /**
     * 精确匹配
     * @param code
     * @return
     */
    public List<String> getAscsidByCode(String code) {
        String sql = "SELECT agentid FROM dev where code = '" + code + "'";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        List<String> agentids = new ArrayList<>();
        for (Map<String, Object> map : maps) {
            agentids.add(map.get("agentid").toString());
        }
        return agentids;
    }

    public List<AscsBaseModel> getAscsNoCode() {
        String sql = "SELECT id, agentid,protocol, geometry, lastTime, descs,type,status,jsonparam,code FROM dev where code is null ";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        return convertAscs(maps);
    }

    public List<AscsBaseModel> getAscsInfoOnline() {

        String sql = "SELECT id, geometry, lastTime, descs,type,status,jsonparam FROM dev WHERE LOCALTIMESTAMP - lastTime < '2 min'";

        List<Map<String, Object>> lvRet = jdbcTemplate.queryForList(sql);

        return convertAscs(lvRet);
    }

    public List<AscsBaseModel> convertAscs(List<Map<String, Object>> lvRet) {
        List<AscsBaseModel> pl = new ArrayList<>();
        for (Map map : lvRet) {

            AscsBaseModel ascBase = new AscsBaseModel();
            ascBase.setState((String) map.get("state"));
            ascBase.setId((int) map.get("id"));
            ascBase.setAgentid((String) map.get("agentid"));
            ascBase.setProtocol((String) map.get("protocol"));
            ascBase.setStatus((int) map.get("status"));
            ascBase.setName((String) map.get("name"));
            ascBase.setType((String) map.get("type"));
            ascBase.setLastTime((Date) map.get("lastTime"));
            ascBase.setCode((String) map.get("code"));
            String geometry = (String) map.get("geometry");
            ascBase.setGeometry(gson.fromJson(geometry, MyGeometry.class));
            JsonObject jsonparam = new JsonParser().parse(map.get("jsonparam").toString()).getAsJsonObject();
            ascBase.setJsonparam(jsonparam);
            pl.add(ascBase);
        }
        return pl;
    }

    public boolean isTableExist(String tableName) {
        Connection conn = null;
        ResultSet rs = null;

        try {
            conn = jdbcTemplate.getDataSource().getConnection();
            DatabaseMetaData data = conn.getMetaData();
            String[] types = {"TABLE"};
            rs = data.getTables(null, null, tableName, types);
            if (rs.next()) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    @Transactional
    public boolean modifyAgentid(String oldAgentid, String newAgentid) {
        try {
            if (isTableExist("dev")) {
                String devSql = "update dev set agentid=? where agentid=?";
                jdbcTemplate.update(devSql, newAgentid, oldAgentid);
            }

            if (isTableExist("fault")) {
                String faultSql = "update fault set agentid=? where agentid=?";
                jdbcTemplate.update(faultSql, newAgentid, oldAgentid);
            }


//            if (isTableExist("third_dev")) {
//                String ht_deviceSql = "update third_dev set agentid=? where agentid=?";
//                jdbcTemplate.update(ht_deviceSql, newAgentid, oldAgentid);
//            }

            if (isTableExist("intersection_dev")) {
                String intersection_devSql = "update intersection_dev set agentid=? where agentid=?";
                jdbcTemplate.update(intersection_devSql, newAgentid, oldAgentid);
            }

            if (isTableExist("operation_record")) {
                String operation_record = "update operation_record set agentid=? where agentid=?";
                jdbcTemplate.update(operation_record, newAgentid, oldAgentid);
            }


            if (isTableExist("t_control")) {
                String t_control = "update t_control set agentid=? where agentid=?";
                jdbcTemplate.update(t_control, newAgentid, oldAgentid);
            }

            if (isTableExist("t_stat")) {
                String t_stat = "update t_stat set agentid=? where agentid=?";
                jdbcTemplate.update(t_stat, newAgentid, oldAgentid);
            }

            if (isTableExist("t_params")) {
                String t_params = "update t_params set agentid=? where agentid=?";
                jdbcTemplate.update(t_params, newAgentid, oldAgentid);
            }

            if (isTableExist("trafficincident")) {
                String trafficincident = "update trafficincident set agentid=? where agentid=?";
                jdbcTemplate.update(trafficincident, newAgentid, oldAgentid);
            }

            if (isTableExist("vip_route_device")) {
                String vip_route_device = "update vip_route_device set agentid=? where agentid=?";
                jdbcTemplate.update(vip_route_device, newAgentid, oldAgentid);
            }

            if (isTableExist("static_route_intersection")) {
                String static_route_intersection = "update static_route_intersection set agentid=? where agentid=?";
                jdbcTemplate.update(static_route_intersection, newAgentid, oldAgentid);
            }

            if (isTableExist("devs_video")) {
                String devs_video = "update devs_video set agentid=? where agentid=?";
                jdbcTemplate.update(devs_video, newAgentid, oldAgentid);
            }

            if (isTableExist("featureall")) {
                String featureall = "update featureall set agentid=? where agentid=?";
                jdbcTemplate.update(featureall, newAgentid, oldAgentid);
            }

            if (isTableExist("pattern")) {
                String pattern = "update pattern set agentid=? where agentid=?";
                jdbcTemplate.update(pattern, newAgentid, oldAgentid);
            }

            redisTemplate.publish("updateIdMap", "UpdateDev:" + newAgentid);
            // 刷新第三方ID列表
            initThirdidToAgentidOcpMap();

        } catch (Exception e) {
            logger.warning("modify Agent id " + oldAgentid + " to:" + newAgentid + " error: " + e.getMessage() );
            return false;
        }

        return true;
    }

    public AscsBaseModel getAscsByGBID(String id) throws EnumConstantNotPresentException {

        AscsBaseModel ascsBaseModel = null;
        String sql =
                "SELECT id, thirdplatformid , platform, gbid, firm, agentid,protocol, geometry,type,status,descs,name, jsonparam,case (LOCALTIMESTAMP - lastTime)< '2 min' when true then 'UP' else 'DOWN' END AS state,lastTime,sockettype FROM dev WHERE gbid ='"
                        + id + "'";
        List<AscsBaseModel> listAscs = getDevByPara(sql);

        if (listAscs.size() == 1) {
            ascsBaseModel = listAscs.get(0);
        }else{
            logger.warning("Search GBID devs error :" + listAscs);
        }

        return ascsBaseModel;
    }


    public AscsBaseModel getAscsByID(String id) throws EnumConstantNotPresentException {

        AscsBaseModel ascsBaseModel = null;
        String sql =
                "SELECT id, thirdplatformid , platform, gbid, firm, agentid,protocol, geometry,type,status,descs,name, jsonparam,case (LOCALTIMESTAMP - lastTime)< '2 min' when true then 'UP' else 'DOWN' END AS state,lastTime,sockettype,auth,secretkey FROM dev WHERE agentid ='"
                        + id + "'";
        List<AscsBaseModel> listAscs = getDevByPara(sql);

        if (listAscs.size() == 1) {
            ascsBaseModel = listAscs.get(0);
        }else{
            logger.warning("Search AgentID devs error , id:" + id);
        }
        return ascsBaseModel;
    }


    public List<AscsBaseModel> getAscsByType(String type) throws EnumConstantNotPresentException {

        String sql = "SELECT id,agentid,protocol, geometry,type,status,descs, name,jsonparam,case (LOCALTIMESTAMP - lastTime)< '2 min' when true then 'UP' else 'DOWN' END AS state FROM dev WHERE type ='" + type + "'";
        List<AscsBaseModel> listAscs = getDevByPara(sql);

        return listAscs;
    }

    public List<AscsBaseModel> getAscsBySiteID(String siteid) {

        String sql = "SELECT id,agentid,protocol, geometry,type,status,descs, name,jsonparam,case (LOCALTIMESTAMP - lastTime)< '2 min' when true then 'UP' else 'DOWN' END AS state ,auth FROM dev WHERE  thirdplatformid ='" + siteid + "'";
        List<AscsBaseModel> listAscs = getDevByPara(sql);

        return listAscs;
    }

    /**
     * @Author: yangyi
     * @Date: 2021/12/21 10:25
     * @Description: getListByAgentids
     */ 
    public List<AscsBaseModel> getListByAgentids(List<String> list) throws EnumConstantNotPresentException {
        List<AscsBaseModel> listAscs = new ArrayList<AscsBaseModel>();
        if (list != null && list.size() > 0) {
            String agentids = getInCond(list);
            String sql =
                "SELECT id, thirdplatformid , platform, gbid, firm, agentid,protocol, geometry,type,status,descs,name, jsonparam,case (LOCALTIMESTAMP - lastTime)< '2 min' when true then 'UP' else 'DOWN' END AS state,lastTime,sockettype FROM dev WHERE agentid in "
                    + agentids;
            listAscs = getDevByPara(sql);
        }
        return listAscs;
    }
    /**
     * @Author: yangyi
     * @Date: 2021/12/21 13:51
     * @Description:
     */
    public static String getInCond(List<String> list) {
        String[] strArr = list.toArray(new String[list.size()]);
        StringBuffer sb = new StringBuffer();
        for(int i = 0;i < strArr.length; i++){
            if(i == strArr.length-1){
                sb.append("'"+strArr[i]+"'");
            }else{
                sb.append("'"+strArr[i]+"'"+",");
            }
        }
        return '(' +sb.toString()+ ')';
    }

    /**
     * @Author: yangyi
     * @Date: 2021/12/21 10:34
     * @Description: getDevFromList
     */ 
    public AscsBaseModel getDevFromList (List<AscsBaseModel> list, String agentid) {
        AscsBaseModel ascsBaseModel = null;
        if (list != null && list.size() > 0 && agentid != null) {
            for (AscsBaseModel dev: list) {
                if (agentid.equals(dev.getAgentid())){
                    ascsBaseModel = dev;
                }
            }
        }
        return ascsBaseModel;
    }

    /**
     * @Author: yangyi
     * @Date: 2021/12/21 14:25
     * @Description: getDevNameFromList
     */ 
    public String getDevNameFromList (List<AscsBaseModel> list, String agentid) {
        AscsBaseModel ascsBaseModel = getDevFromList(list, agentid);
        String agentName = "";
        if (ascsBaseModel != null) {
            agentName = ascsBaseModel.getName();
            if (agentName == null || agentName.equals("")) {
                agentName = ascsBaseModel.getAgentid();
            }
        }
        return  agentName;
    }


    public List<String> getFaultDev() {
        String sql = "select DISTINCT(agentid) from fault where m_un_fault_renew_time = 0";
        List<String> list = jdbcTemplate.queryForList(sql, String.class);
        return list;
    }

    public List<AscsBaseModel> getDevByPara(String sql){
//        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        List<Map<String, Object>> lvRet = jdbcTemplate.queryForList(sql);
        List<AscsBaseModel> abm = new ArrayList<>();
        for (Map map : lvRet) {
            AscsBaseModel ascsBaseModel = new AscsBaseModel();
            ascsBaseModel.setId((int) map.get("id"));
            ascsBaseModel.setThirdplatformid((String) map.get("thirdplatformid"));
            ascsBaseModel.setPlatform((String) map.get("platform"));
            ascsBaseModel.setGbid((String) map.get("gbid"));
            ascsBaseModel.setFirm((String) map.get("firm"));
            ascsBaseModel.setAgentid((String) map.get("agentid"));
            ascsBaseModel.setProtocol((String) map.get("protocol"));
            ascsBaseModel.setType((String) map.get("type"));
            ascsBaseModel.setDescs((String) map.get("descs"));
            ascsBaseModel.setStatus((int) map.get("status"));
            String geometry = (String) map.get("geometry");
            ascsBaseModel.setCode((String) map.get("code"));
            ascsBaseModel.setGeometry(gson.fromJson(geometry, MyGeometry.class));
            ascsBaseModel.setState((String) map.get("state"));
            ascsBaseModel.setTags((String) map.get("tags"));
            ascsBaseModel.setPhaseCount((Integer) map.get("phase_count"));
            ascsBaseModel.setPatternCount((Integer) map.get("pattern_count"));
            ascsBaseModel.setPlanCount((Integer) map.get("plan_count"));
            ascsBaseModel.setDateCount((Integer) map.get("date_count"));
            ascsBaseModel.setOverlapCount((Integer) map.get("overlap_count"));
            ascsBaseModel.setVehLampCount((Integer) map.get("veh_lamp_count"));
            ascsBaseModel.setPedLampCount((Integer) map.get("ped_lamp_count"));
            ascsBaseModel.setAuth((Integer) map.get("auth"));
            ascsBaseModel.setModel((String) map.get("model"));
            ascsBaseModel.setSecretkey((String) map.get("secretkey"));


            Object value = map.get("lastTime");
            if (value != null) {
//                try {
//                    ascsBaseModel.setLastTime(sdf.parse(value.toString()));
//                } catch (ParseException e) {
//                    ascsBaseModel.setLastTime(null);
//                }
                Timestamp ts = (Timestamp) value;
                ascsBaseModel.setLastTime(ts);
            }
            ascsBaseModel.setName((String) map.get("name"));

            value = map.get("jsonparam");
            if (value != null){
                try {
//                    JsonObject jsonparam = new JsonParser().parse(value.toString()).getAsJsonObject();
                    JsonObject jsonparam = gson.fromJson(value.toString(),JsonObject.class);
                    ascsBaseModel.setJsonparam(jsonparam);
                } catch (JsonSyntaxException e){
                    ascsBaseModel.setJsonparam(null);
                }
            }

            value = map.get("event_type");
            if (value != null){
                PgArray ary = (PgArray)value;
                Integer[] intary = new Integer[0];
                try {
                    intary = (Integer[]) ary.getArray();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
                ascsBaseModel.setEvent_type(intary);
            }
            Integer integer = (Integer) map.get("sockettype");
            ascsBaseModel.setSockettype(integer == null ? 0 : integer.intValue());
            ascsBaseModel.setMode((Integer) map.get("mode"));
            ascsBaseModel.setControl((Integer) map.get("control"));
            abm.add(ascsBaseModel);
            // 从Redis中获取当前控制方式
//            StatusPattern statusPattern = redisTemplate.getStatusPatternFromRedis(ascsBaseModel.getAgentid());
//            if(statusPattern != null){
//                ascsBaseModel.setMode(statusPattern.getMode());
//                ascsBaseModel.setControl(statusPattern.getControl());
//            }

        }
        return abm;
    }

    @Transactional
    public int deleteDevByID(String id) {
        int rows = 0;
        String sql = "DELETE FROM dev WHERE agentid ='" + id + "'";
        rows = jdbcTemplate.update(sql);

        if (isTableExist("devs_video")) {
            sql = "DELETE FROM devs_video WHERE agentid ='" + id + "'";
            rows = jdbcTemplate.update(sql);
        }

        if (isTableExist("featureall")) {
            sql = "DELETE FROM featureall WHERE agentid ='" + id + "'";
            rows = jdbcTemplate.update(sql);
        }

        // 刷新第三方ID列表
        initThirdidToAgentidOcpMap();

        return rows;
    }


    public AscsBaseModel insertDev(AscsBaseModel ascs) {
        String geometry = null;
        if (ascs.getGeometry() != null){
            geometry = ascs.getGeometry().toString();
        }
        String jsonParam = null;
        if (ascs.getJsonparam() != null){
            jsonParam = ascs.getJsonparam().toString();
        }
        String sql = "INSERT INTO dev(agentid,thirdplatformid,gbid,name,descs,type,protocol,sockettype,firm,platform,geometry,tags,jsonparam,auth) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,to_json(?::json),?)";
        jdbcTemplate.update(sql,
                ascs.getAgentid(),
                ascs.getThirdplatformid(),
                ascs.getGbid(),
                ascs.getName(),
                ascs.getDescs(),
                ascs.getType(),
                ascs.getProtocol(),
                ascs.getSockettype(),
                ascs.getFirm(),
                ascs.getPlatform(),
                geometry,
                ascs.getTags(),
                jsonParam,
                ascs.getAuth()
                );

        redisTemplate.publish("updateIdMap", "UpdateDev:" + ascs.getAgentid());
        // 刷新第三方ID列表
        initThirdidToAgentidOcpMap();
        
        return ascs;
    }


    public int getCountByAgentid(String agentid) {
        String sql = "SELECT count(id) FROM dev where agentid = ?";
        int count = jdbcTemplate.queryForObject(sql, int.class, agentid);
        return count;
    }

    // 查询数据库连接数
    public int getConnectCount() {
        String sql = "select count(1) from pg_stat_activity";
        int count = jdbcTemplate.queryForObject(sql, int.class);
        return count;
    }

    // 查询数据库大小
    public String getDBSize(String db) {
        String sql = "select pg_size_pretty(pg_database_size( ? ));";
        String size = jdbcTemplate.queryForObject(sql, String.class, db);
        return size;
    }


    public int updateDev(AscsBaseModel ascs) {
        //判断是否存在
        long count = getCountByAgentid(ascs.getAgentid());
        //ID已存在，更新注册信息,不存在，返回；
        if (count == 0)
            return 0;
        // 更新第三方平台的信息
//        String sql = "UPDATE third_dev set type=? where agentid=?";
//        int res = 0;
//        try{
//            jdbcTemplate.update(sql,
//                    ascs.getPlatform(),
//                    ascs.getAgentid() );
//        }catch (Exception e){
//            logger.warning("UPDATE third_dev error! " + e.getMessage());
//        }


        String sql = "update dev set thirdplatformid=?,gbid=?,platform=?, firm=?, name=?, type=? ,protocol=? ,descs=? ,geometry= ?,jsonparam=(to_json(?::json)),code=?,sockettype=?,tags=?,auth=? where agentid=?";
        String geometry = null;
        if (ascs.getGeometry() != null) {
            geometry = ascs.getGeometry().toString();
        }
        int res = jdbcTemplate.update(sql,
                ascs.getThirdplatformid(),
                ascs.getGbid(),
                ascs.getPlatform(),
                ascs.getFirm(),
                ascs.getName(),
                ascs.getType(),
                ascs.getProtocol(),
                ascs.getDescs(),
                geometry,
                ascs.getJsonparam().toString(),
                ascs.getCode(),
                ascs.getSockettype(),
                ascs.getTags(),
                ascs.getAuth(),
                ascs.getAgentid()
        );

        // 刷新第三方ID列表
        initThirdidToAgentidOcpMap();

        return res;
    }


    /**
     * 更新dev表中的code字段
     * @param currentOrgDevs 需要加到当前机构中的设备
     * @param needRemoveDevs 需要从当前设备中移除的设备
     * @param code
     */
    public void updateDevByCode(List<String> currentOrgDevs, List<String> needRemoveDevs, String code) {
        List<String> sqls = new ArrayList<>();

        if (!CollectionUtils.isEmpty(currentOrgDevs)) {
            StringBuffer needAddCurrentOrg = getBuffer(currentOrgDevs);
            String addSql = String.format("update dev set code = '%s' where agentid in (%s)",code,needAddCurrentOrg);
            sqls.add(addSql);
        }
        if (!CollectionUtils.isEmpty(needRemoveDevs)) {
            StringBuffer needRemove = getBuffer(needRemoveDevs);
            String subSql = String.format("update dev set code = NULL where agentid in (%s)",needRemove);
            sqls.add(subSql);
        }

        if (!CollectionUtils.isEmpty(sqls)) {
            batchUpdate(sqls);
        }

    }



    public List<String> getAgentidsBySql(String sql) {
        List<String> agentids = jdbcTemplate.queryForList(sql, String.class);
        return agentids;
    }

    public int insertAscsByReportLogin(DevCover devCover, String newAgentid,String siteid){

        String agentid = newAgentid;
        if(devCover== null || agentid == null || siteid == null){
            logger.warning("Insert Ascs by Login Msg Error ! agentid = null, devCover = " + devCover);
            return 0;
        }

        AscsBaseModel ascsModel = getAscsBaseModel(devCover,newAgentid,siteid);

        String sql = "INSERT INTO dev(agentid, name, thirdplatformid,type,status,protocol,platform,geometry,jsonparam,lastTime,model ) VALUES (?,?,?,?,?,?,?,?,to_json(?::json),LOCALTIMESTAMP,?)";
        int rows = jdbcTemplate.update(sql,
                ascsModel.getAgentid(),
                ascsModel.getName(),
                ascsModel.getThirdplatformid(),
                ascsModel.getType(),
                ascsModel.getStatus(),
                ascsModel.getProtocol(),
                ascsModel.getPlatform(),
                ascsModel.getGeometry().toString(),
                ascsModel.getJsonparam().toString(),
                ascsModel.getModel());

        // 发布设备更新消息
        if (rows == 0){
            logger.warning("insert Ascs by Login Msg Error ! agentid is exsit! devCover = " + devCover);
        }
        else{
            thirdidToAgentidOcp.put(siteid,agentid );
            redisTemplate.publish("updateIdMap", "UpdateDev:" + ascsModel.getAgentid());
        }
        return rows;
    }


    /**
     * 处理设备上报的注册消息
     * 1、第三方平台：scats、1049上报的消息会携带agentid
     * 2、没有携带agentid的视为openatc的信号机
     * 3、平台3.0版本不再支持多个信号机使用相同设备ID
     * 4、openatc的信号机将使用设备id，作为agentid
     * 5、设备的注册统一由定时任务处理
     * 6、存在线程安全问题，增删改路口时，会先清空thirdidToAgentidOcp，如果此时收到信号机上报的消息，
     *      从缓存获取到agentid为null，会认为是新的agentid，并新建路口id，并保存到数据库
     * @param msg
     * @return
     */
    public String processLoginMsg(MessageData msg) {
        String msgAgentid = msg.getAgentid();
        // 第三方平台上报的注册消息，会带agentid，直接返回
        if (msgAgentid != null) {
            msg.setModel(SCP_PROTYPE);
            return msgAgentid;
        }

        msg.setModel(OCP_PROTYPE);
        String thirdpartyid =  msg.getThirdpartyid();

        try {
            thridAgentCacheLock.lock();

            String agentid = getAgentidFromThirdPartyid(thirdpartyid);
            // 缓存中没有agentid，则表明是新的设备
            if (agentid == null) {
                agentid = thirdpartyid;
                DevCover devCover = gson.fromJson(msg.getData(),DevCover.class);
                int ret = insertAscsByReportLogin(devCover,thirdpartyid,thirdpartyid);
                if(ret == 0)
                    logger.warning("process Login Msg Error !  Msg:" + msg );
            }
            msg.setAgentid(agentid);
            return agentid;
        } catch (Exception e) {
            logger.warning("process Login Msg Error !  Msg:" + msg);
            return null;
        }
        finally {
            thridAgentCacheLock.unlock();
        }
    }

    /**
     *
     * @param thirdpartyid
     * @return
     */
    public String getAgentidFromThirdPartyid(String thirdpartyid) {
            return thirdidToAgentidOcp.get(thirdpartyid);
    }

    /**
     * @Author zhangwenchao
     * @Description 获取分页过滤路口列表
     * @Date 2022/1/12 16:48
    */
    public PageOR<AscsBaseModel> getAscsRange(JsonObject jsonObject) {
        PageOR<AscsBaseModel> pageOR = new PageOR<>();
        if (jsonObject == null) {
            return null;
        }

        // 分页条件
        int pageNum = jsonObject.get("pageNum").getAsInt() - 1;
        int pageRow = jsonObject.get("pageRow").getAsInt();
        if (pageNum < 0)
            pageNum = 0;
        String limitCondition = String.format(" limit  %d  offset  %d ",pageRow, pageNum*pageRow );

        // 查询条件
        String whereCondition = "";
        // 根据用户角色返回对应sql
        String sql = getSqlByUserRole();
        JsonElement org_codeElement = jsonObject.get("code");
        if (sql != null){
            // 组织机构过滤条件
            if (org_codeElement != null && StringUtils.hasLength(org_codeElement.getAsString())) {
                String org_code = org_codeElement.getAsString();
                sql = computeOrgCode(org_code,sql);
            }
            // 程序运行到此处，sql被重置为null，说明没有满足当前过滤条件的设备，直接应答空
            if (sql == null) {
                return new PageOR<>();
            }
            String temp = sql;
            whereCondition = addWhereCondition(whereCondition,temp);
        } else {
            // 组织机构过滤条件
            if (org_codeElement != null && StringUtils.hasLength(org_codeElement.getAsString())) {
                String org_code = org_codeElement.getAsString();
                if (!"*".equals(org_code)) {
//                    String temp = String.format("code='%s'",org_code);
                    String temp = String.format("code like '%%%s%%'",org_code);
                    whereCondition = addWhereCondition(whereCondition,temp);
                }
            }
        }
        String search = jsonObject.get("search").getAsString();
        if( !search.isEmpty()){
            String temp = String.format("( agentid like '%%%s%%' or name like '%%%s%%' or thirdplatformid like '%%%s%%' or jsonparam::text like '%%%s%%' ) ",search,search,search,search);
            whereCondition = addWhereCondition(whereCondition,temp);
        }
        String type = jsonObject.get("type").getAsString();
        if( !type.isEmpty()){
            String temp = String.format("type = '%s'",type);
            whereCondition = addWhereCondition(whereCondition,temp);
        }
        String platform = jsonObject.get("platform").getAsString();
        if( !platform.isEmpty()){
            String temp = String.format("platform = '%s'",platform);
            whereCondition = addWhereCondition(whereCondition,temp);
        }
        String protocol = jsonObject.get("protocol").getAsString();
        if( !protocol.isEmpty()){
            String temp = String.format("protocol = '%s'",protocol);
            whereCondition = addWhereCondition(whereCondition,temp);
        }
        String tags = jsonObject.get("tags").getAsString();
        if( !tags.isEmpty()){
            String temp = String.format("tags like '%%%s%%'",tags);
            whereCondition = addWhereCondition(whereCondition,temp);
        }

        // 状态条件
        String state = jsonObject.get("state").getAsString();

        // 控制模式条件
        int mode = jsonObject.get("mode").getAsInt();
        // 默认值传-1(不添加该条件)
        if (mode != -1){
            // 存在离线条件
            if (state.equals("DOWN")){
                pageOR.setTotal(0l);
                return pageOR;
            }
            // 没有状态过滤条件，手动添加在线
            else if (state.isEmpty()){
                state = "UP";
            }
            String temp = String.format("mode = '%s'",mode);
            whereCondition = addWhereCondition(whereCondition,temp);
        }
        // 控制方式条件
        int control = jsonObject.get("control").getAsInt();
        if (control != -1){
            // 存在离线条件
            if (state.equals("DOWN")){
                pageOR.setTotal(0l);
                return pageOR;
            }
            // 没有状态过滤条件，手动添加在线
            else if (state.isEmpty()){
                state = "UP";
            }
            String temp = String.format("control = '%s'",control);
            whereCondition = addWhereCondition(whereCondition,temp);
        }
        if( !state.isEmpty()){
            String temp;
            if(state.equals("DOWN"))
                temp = "((LOCALTIMESTAMP - lastTime)> '2 min' or lasttime is null)";
            else
                temp = "(LOCALTIMESTAMP - lastTime)< '2 min'";
            whereCondition = addWhereCondition(whereCondition,temp);

        }

        // 区域条件过滤
//        whereCondition = getAreaCondition(jsonObject,whereCondition);

        // 路线过滤条件
//        whereCondition = getRouteCondition(jsonObject,whereCondition);

        // 事件类型过滤条件
        JsonElement jeEventType = jsonObject.get("eventtype");
        if(jeEventType != null){
            int event_type = jeEventType.getAsInt();
            if( event_type != -1 ){
                String temp = String.format("%d=ANY(event_type)",event_type);
                whereCondition = addWhereCondition(whereCondition,temp);
            }
        }

        // 获取总数
        String countsql = "SELECT count(*) FROM dev ";
        countsql = countsql.concat(whereCondition);
        int count = jdbcTemplate.queryForObject(countsql, int.class);
        pageOR.setTotal((long) count);
        if(count == 0){ //没有查询记录直接返回
            return pageOR;
        }

        // 获取分页记录
        String searchsql = "SELECT id, thirdplatformid, platform, gbid, firm, agentid, protocol, geometry, type, code,mode,control, status, descs, name,jsonparam, auth, model, " +
                "case (LOCALTIMESTAMP - lastTime)< '2 min' when 'true' then 'UP' else 'DOWN' END AS state,lastTime,sockettype,tags,event_type " +
                "FROM dev ";
        searchsql = searchsql.concat(whereCondition);

        // 排序规则
        searchsql = searchsql.concat(" ORDER BY ");
        if(jeEventType != null) // 有事件查询，则按事件排序，发生事件的路口排在前面
            searchsql = searchsql.concat("event_type,");
        searchsql = searchsql.concat("agentid");

        // 当记录条数小于每页条数时，无需再设置offset
        if (count < pageRow) {
            limitCondition = String.format(" limit  %d ",pageRow);
        }
        searchsql = searchsql.concat(limitCondition);
        List<AscsBaseModel> ascsBaseModels = getDevByPara(searchsql);
        pageOR.setContent(ascsBaseModels);
        return pageOR;
    }

    /**
     * 处理组织机构和用户角色的交集
     * @param org_code
     * @param sql
     * @return
     */
    private String computeOrgCode(String org_code, String sql) {
        // 下发跟组织，返回原始的sql
        if ("*".equals(org_code)) {
            return sql;
        }
        if (sql.contains("in")) {
            String substring = sql.substring((sql.indexOf("(") + 1), sql.indexOf(")"));
            String s = substring.replaceAll("'", "");
            String[] split = s.split(",");
            List<String> strings = Arrays.asList(split);
            if (!strings.contains(org_code)) {
                return null;
            } else {
                return "code in " + "('" + org_code +"')";
            }
        } else {
            String substring = sql.substring(sql.indexOf("=") + 1);
            String s = substring.replaceAll("'", "");
            if (!s.equals(org_code)) {
                return null;
            } else {
                return sql;
            }
        }
    }

    /**
     * 处理路线过滤条件
     * @param jsonObject
     * @param whereCondition
     * @return
     */
    private String getRouteCondition(JsonObject jsonObject, String whereCondition) {



        // 路线条件
        JsonElement routeidElement = jsonObject.get("routeid");
        if (routeidElement != null) {

        }
        return "";
    }

    /**
     * 处理区域过滤条件
     * @param jsonObject
     * @param whereCondition
     * @return
     */
    private String getAreaCondition(JsonObject jsonObject, String whereCondition) {
        // 区域条件
        JsonElement areaidElement = jsonObject.get("areaid");
        if (areaidElement != null) {
            int areaid = areaidElement.getAsInt();
            // 区域中有路线，有独立路口
            // 先查询出区域中独立路口

            // 再查询出区域中路线中包含的路口

        }
        return "";
    }

    /**
     * 根据用户角色返回不同的sql
     * @return
     */
    public String getSqlByUserRole() {
        // 管理员列表
        List<String> adminRoles = new ArrayList<>();
        adminRoles.add("superadmin");
        adminRoles.add("admin");
//        adminRoles.add("expert");

        // 获取当前登录用户信息
        User user = (User) SecurityUtils.getSubject().getPrincipal();
        // 未开启shiro
        if (user == null){
            return null;
        }
        // 获取用户名
        String user_name = user.getUser_name();
        List<String> roles = userDao.getRoleNamesByUsername(user_name);
        adminRoles.retainAll(roles);

        // 非管理员
        if (adminRoles.size() == 0){
            user = userDao.getUserByUserName(user_name);
            String organization = user.getOrganization();
            // 用户属于多个组织（前端只能选中一个或所有）
            if ("*".equals(organization)){  // * 代表所有组织（“”除外）
                return null;
//                String selectsql = "select orgnization_code from t_orgnization";
//                List<String> strings = jdbcTemplate.queryForList(selectsql, String.class);
//                if (strings.isEmpty()){
//                    return null;
//                }
//
//                StringBuffer sb = getBuffer(strings);
//
//                String sql = String.format("code in (%s)",sb);
//
//                return sql;
            }
            // 用户属于一个组织/不属于任何组织机构)
            else {
                if (!StringUtils.hasLength(organization)) {
                    return  "code is null";
                }
//                String sql = String.format("code='%s'",organization);
                String sql = String.format("code like '%%%s%%'",organization);
                return sql;
            }

        }

        return null;
    }

    // 拼接sql中in后面的字符
    private StringBuffer getBuffer(List<String> strings) {
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < strings.size(); i++){
            if (i != strings.size() - 1){
                sb.append("'" + strings.get(i) + "',");
            }else {
                sb.append("'"+ strings.get(i) + "'");
            }
        }
        return sb;
    }


    /**
     * @Author zhangwenchao
     * @Description 添加where过滤条件
     * @Date 2022/1/13 10:16
    */
    public String addWhereCondition(String whereCondition, String temp) {
        if(whereCondition == null)
            whereCondition = "";

        if(whereCondition.isEmpty()) {
            whereCondition = whereCondition.concat(" where ");
            whereCondition = whereCondition.concat(temp);
        }
        else{
            whereCondition = whereCondition.concat(" and ");
            whereCondition = whereCondition.concat(temp);
        }

        return  whereCondition;
    }


    /**
     * 根据组织机构代码查询设备
     * @param ascss
     * @param orgnization_code
     * @return
     */
    public List<AscsBaseModel> getAscsByOrgCode(List<AscsBaseModel> ascss, String orgnization_code) {
        List<AscsBaseModel> ascsBaseModels = new ArrayList<>();
        // 用户不属于任何组织机构
        if (orgnization_code.equals("")){
            for (AscsBaseModel ascs : ascss){
                String code = ascs.getCode();
                if (code == null){
                    ascsBaseModels.add(ascs);
                }
            }
            return ascsBaseModels;
        }
        // 用户属于某个组织机构
        for (AscsBaseModel ascs : ascss){
            String code = ascs.getCode();
            if (orgnization_code.equals(code)){
                ascsBaseModels.add(ascs);
            }
        }
        return ascsBaseModels;

    }

    /**
     * 批量更新数据数据
     * jdbcTemplate.batchUpdate使用hikari中的connection，默认AutoCommit为true，每条语句执行都会commit，导致执行效率低
     * 手动实现batchUpdate代替
     * @param sqls
     */
    public void batchUpdate(List<String> sqls) {

        if(sqls == null || sqls.size() == 0)
            return;

//        String[] strings = sqls.toArray(new String[]{});
//        jdbcTemplate.batchUpdate(strings);
        Connection connection = null;
        Statement statement = null;
        try {
//            logger.warning("Start batch update!");
            long startTime = System.currentTimeMillis();
            connection =  jdbcTemplate.getDataSource().getConnection();
            connection.setAutoCommit(false);

            statement = connection.createStatement();
            for (String sql : sqls) {
                statement.addBatch(sql);
            }
            statement.executeBatch();
            connection.commit();
//            long endTime = System.currentTimeMillis();
//            logger.warning("batch update end!");
//            logger.warning("batch update success: " + (endTime - startTime) + "ms " + " sql size:" + sqls.size() );
        } catch (SQLException throwables) {
            logger.warning("batch update error!" + throwables.getMessage());
            try {
                connection.rollback();
                logger.warning("connection rollback!");
            } catch (SQLException e) {
                logger.warning("connection rollback error!" + e.getMessage());
            }
        } finally {
            if (statement != null) {
                try {
                    statement.clearBatch();
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }

    /**
     * 保存路口渠化图
     * @param agentid
     * @param data
     * @return
     */
    public int saveIntChannelizatonChart(String agentid, JsonElement data) {
//        // 保证数据库中每个设备最多有一条记录
//        LocalDateTime now = LocalDateTime.now();
//        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
//        String time = dtf.format(now);
//        String sql = String.format("INSERT INTO t_channelization (agentid,time,data) VALUES ('%s','%s','%s')",agentid,time,data);
//        sql = sql + String.format("ON CONFLICT(agentid) DO UPDATE SET time='%s',data='%s'",time,data);
        String sql = String.format("UPDATE dev SET channelization='%s' where agentid='%s'",data,agentid);
        return jdbcTemplate.update(sql);
    }

    /**
     * 获取路口渠化图
     * @param agentid
     * @return
     */
    public String getIntChannelizationChartByAgentid(String agentid) {
        String sql = String.format("select channelization from dev where agentid='%s'",agentid);
        try {
            return jdbcTemplate.queryForObject(sql, String.class);
        } catch (DataAccessException e){
            return null;
        }
    }

    /**
     * 从redis中获取路口的检测器状态数据
     * @param key
     * @return
     */
    public String getValueFromRedis(String key) {
        return redisTemplate.getValue(key);
    }

    /**
     * 将数据保存到redis
     * @param key
     * @param value
     */
    public void saveValueToRedis(String key, String value) {
        redisTemplate.setValue(key,value);
    }

    /**
     * 保存相位统计数据
     * @param data 数据
     * @param agentid 路口id
     * @param dataLabel 数据标识
     */
    public void saveStatistics(String data, String agentid, String dataLabel) {
        String time = DateUtil.date2str(new Date());
        String sql = String.format("insert into t_statistics (agentid,time,data,data_label) values ('%s','%s','%s','%s')",agentid,time,data,dataLabel);
        jdbcTemplate.update(sql);
    }

    /**
     * 从数据库中获取统计数据
     * @param agentid 路口id
     * @param dataLabel 数据标识（相位统计数据：phasesStatistics；路口统计数据：intersectionStatistics）
     * @param beginTime 开始时间
     * @param endTime 结束时间
     * @return
     */
    public List<String> getStatistics(String agentid, String dataLabel,String beginTime,String endTime) {
        beginTime = String.format("to_timestamp('%s','%s')",beginTime,"yyyy-MM-dd HH24:mi:ss");
        endTime = String.format("to_timestamp('%s','%s')",endTime,"yyyy-MM-dd HH24:mi:ss");
        String sql = String.format("select data from t_statistics where agentid='%s' and data_label='%s' and time between %s and %s order by time",
                agentid,dataLabel,beginTime,endTime);
        return jdbcTemplate.queryForList(sql,String.class);
    }

    public void publishMsg(String key, String value) {
        redisTemplate.publish(key,value);
    }

    /**
     * 添加路口事件
     * @param agentid
     * @param eventType
     * @param eventTime
     * @return
     */
    public int addEvent(String agentid, int eventType, String eventTime) {
        String sql = String.format("update dev  set event_type = array_remove(event_type,%s)||%s where agentid = '%s'",eventType,eventType,agentid);
        return jdbcTemplate.update(sql);

    }
    /**
     * 删除路口事件
     * @param agentid
     * @param eventType
     * @return
     */
    public int removeEvent(String agentid, int eventType) {
        int ret = 0;
        // 删除对应事件
        String sql = String.format("update dev  set event_type = array_remove(event_type,%s) where agentid = '%s'",eventType,agentid);
        ret = jdbcTemplate.update(sql);

        // 修改bug：路口有新事件后未置顶显示。将删除后空的数组置为null
        if(ret != 0){
            sql = String.format("update dev set event_type = null where event_type = '{}'::int[] and agentid = ''%s'' ",agentid);
            ret = jdbcTemplate.update(sql);
        }

        return ret;

    }
    /**
     * 重置路口事件
     * @param agentid
     * @return
     */
    public int resetEvent(String agentid) {
        String sql = String.format("update dev  set event_type = null where agentid = '%s'",agentid);
        return jdbcTemplate.update(sql);
    }

    public Map<String, Object> getSectionByAgentId(String agentid) {
        String sql = String.format("select count(*) from t_section where sectionid='%s'",agentid);
        return jdbcTemplate.queryForMap(sql);
    }

    public List<String> findOfflineDevId(Long id) {
        String sql = String.format("SELECT agentid FROM dev WHERE agentid IN (SELECT agentid FROM static_route_intersection WHERE route_id =%s) AND ((LOCALTIMESTAMP - lastTime) > '2 min' OR lastTime IS NULL)",id);
        return jdbcTemplate.queryForList(sql,String.class);
    }

    /**
     * 更新dev表中特征参数的数量
     *
     * @param field
     * @param agentid
     * @param count
     * @return
     */
    public int updateFeatureCount(GenericMsgStrategy.FieldName field, String agentid, int count) {
        String fieldName = field.name();
        String sql = String.format("update dev set " + fieldName + "=%s where agentid='%s'",count,agentid);
        return jdbcTemplate.update(sql);
    }

    public int updateAllFeatureCount(String sql) {
        return jdbcTemplate.update(sql);
    }


    /**
     * 根据路口id列表获取路口信息
     * @param routeIntersectionIdList 路口列表
     * @return
     */
    public List<AscsBaseModel> getDevListByIds(List<String> routeIntersectionIdList) {

        StringBuffer inCondition = getBuffer(routeIntersectionIdList);

        String searchsql = String.format("SELECT id, thirdplatformid, platform, gbid, firm, agentid, protocol, geometry, type, code,mode,control, status, descs, name," +
                "jsonparam, case (LOCALTIMESTAMP - lastTime)< '2 min' when 'true' then 'UP' else 'DOWN' END AS state,lastTime,sockettype,tags,event_type " +
                "FROM dev where agentid in (%s) ",inCondition);

        return getDevByPara(searchsql);
    }

    public int updateSecret(String agentid,String secretkey) {

        //更新数据
        String sql = "update dev set secretkey=? , auth=2 where agentid = ?";
        int rows = jdbcTemplate.update(sql,
                secretkey,
                agentid );

        // update失败，则说明表中数据不存在
        if (rows == 0) {
            logger.warning("updateSecret Error ! agentid is not exsit : " + agentid );
        }
        return rows;
    }

    public AscsBaseModel getAscsBaseModel(DevCover devCover, String newAgentid,String siteid) {

        AscsBaseModel ascsModel = new AscsBaseModel();
        ascsModel.setType(devCover.getType());
        ascsModel.setProtocol(devCover.getProtocol());
        ascsModel.setThirdplatformid(siteid);
        ascsModel.setAgentid(newAgentid);

        if (devCover.getPlatform() == null) {
            ascsModel.setPlatform("");
        } else {
            ascsModel.setPlatform(devCover.getPlatform());
        }

        ascsModel.setName(siteid);
        ascsModel.setStatus(devCover.getStatus());

        // 设备地理信息
        MyGeometry myGeometry = new MyGeometry();
        myGeometry.setType("Point");
        myGeometry.setCoordinates(new double[]{devCover.getLat(), devCover.getLng()});
        ascsModel.setGeometry(myGeometry);

        // 设备拓展参数
        JsonObject jo = new JsonObject();
        jo.addProperty("ip", devCover.getIp());
        jo.addProperty("port", devCover.getPort());
        ascsModel.setJsonparam(jo);

        return ascsModel;
    }
}
